package com.matt.repository;

import com.matt.domain.ChildrenHealthBaseInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/11/1
 * *******************************
 */
public interface ChildrenHealthBaseInfoRepository extends JpaRepository<ChildrenHealthBaseInfo, String>, JpaSpecificationExecutor<ChildrenHealthBaseInfo> {
    List<ChildrenHealthBaseInfo> findByChildrenIdOrderByCreatedDesc(String childrenId);

    @Query("select i from ChildrenHealthBaseInfo i where i.childrenId=:childrenId and i.evaluateTime >= :beginDate and i.evaluateTime<=:endDate and i.physicalHeight IS NOT NULL and i.physicalWeight IS NOT NULL order by i.evaluateTime ")
    List<ChildrenHealthBaseInfo> findByChildrenIdAndEvaluateTimeBetween(@Param("childrenId") String childrenId, @Param("beginDate") Date beginDate, @Param("endDate") Date endDate);

    @Query(value = "select * from children_health_base_info i where i.childrenId=:childrenId and i.evaluateTime >= DATE_SUB(now(), interval 15 day)" +
            "and i.physicalHeader IS NOT NULL and i.physicalHeight IS NOT NULL " +
            "and i.physicalHeader > 0 and i.physicalHeight > 0 " +
            "and i.physicalWeight IS NOT NULL and i.physicalWeight > 0 " +
            "order by i.evaluateTime desc ", nativeQuery = true)
    List<ChildrenHealthBaseInfo> findNearestReportIn15Days(@Param("childrenId") String childrenId);

    @Query(value = "select * from children_health_base_info i where i.childrenId=:childrenId and i.evaluateTime >= DATE_SUB(:date, interval 10 day) and i.evaluateTime <= DATE_ADD(:date, interval 10 day) " +
            "and i.physicalHeight IS NOT NULL and i.physicalHeight > 0 " +
            "and i.physicalWeight IS NOT NULL and i.physicalWeight > 0 " +
            "order by i.evaluateTime desc ", nativeQuery = true)
    List<ChildrenHealthBaseInfo> findNearestReportIn15Days(@Param("date") Date date, @Param("childrenId") String childrenId);
}
